﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace FlowCard
{
    public class SQLTools
    {
        /// <summary>
        /// 默认服务器地址
        /// </summary>
        static string _strDefaultServerAddress = "192.168.1.1";
        //static string _strDefaultServerAddress = ".";

        /// <summary>
        /// 默认连接字符串
        /// </summary>
        static string _strDefaultConnectionString = "Data Source=@Server;Initial Catalog=@DBName;Persist Security Info=True;User ID=@UserName;Password=@Password";

        /// <summary>
        /// 连接字符串
        /// </summary>
        private string _strConnectionString;

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection _Conn;

        /// <summary>
        /// 错误消息
        /// </summary>
        private string _ErrMessage;

        /// <summary>
        /// 是否显示错误消息
        /// </summary>
        private bool _ShowErrMessages = true;

        #region 属性
        //连接字符串
        public string ConnectionString
        {
            set
            {
                _strConnectionString = value;
            }
            get
            {
                return _strConnectionString;
            }
        }

        //连接状态
        public System.Data.ConnectionState ConnectionState
        {
            get
            {
                return _Conn.State;
            }
        }

        //获取错误消息
        public string ErrMessage
        {
            get
            {
                return _ErrMessage;
            }
        }

        //是否显示错误消息
        public bool ShowErrMessages
        {
            set
            {
                _ShowErrMessages = value;
            }
            get
            {
                return _ShowErrMessages;
            }
        }

        //获取连接
        public SqlConnection GetConnection()
        {
            return _Conn;
        }
        #endregion 属性

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="strTmpConnectionString">连接字符串</param>
        public SQLTools(string strTmpConnectionString)
        {
            ConnectionString = strTmpConnectionString;
            iniConnection();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="strTmpServer">服务器地址</param>
        /// <param name="strTmpDBName">数据库名称</param>
        /// <param name="strTmpUserName">用户名</param>
        /// <param name="strTmpPassword">密码</param>
        public SQLTools(string strTmpServer, string strTmpDBName, string strTmpUserName, string strTmpPassword)
        {
            ConnectionString = _strDefaultConnectionString.Replace("@Server", strTmpServer)
                                                                                        .Replace("@DBName", strTmpDBName)
                                                                                        .Replace("@UserName", strTmpUserName)
                                                                                        .Replace("@Password", strTmpPassword);
            iniConnection();
        }

        /// <summary>
        /// 构造函数，调用默认参数
        /// </summary>
        public SQLTools()
        {
            ConnectionString = _strDefaultConnectionString.Replace("@Server", _strDefaultServerAddress )
                                                                                        .Replace("@DBName", "TPAMain")
                                                                                        .Replace("@UserName", "sa")
                                                                                        .Replace("@Password", "856518armyman");
            iniConnection();
        }
        #endregion

        #region 方法
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        private void iniConnection()
        {
            _Conn = new SqlConnection(_strConnectionString);
            try
            {
                OpenConnection();
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        private void OpenConnection()
        {
            try
            {
                _Conn.Open();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        /// <param name="conn">要打开的连接</param>
        public void OpenConnection(SqlConnection conn)
        {
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        private void CloseConnection()
        {
            try
            {
                _Conn.Close();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="conn">要关闭的连接</param>
        public void CloseConnection(SqlConnection conn)
        {
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        ///获取服务器日期
        /// </summary>
        /// <returns>yyyyMMdd格式的日期</returns>
        public string GetServerDate()
        {
            return DoCmdScalarData("Select CONVERT(varchar(100), GETDATE(), 112)").ToString ();
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <returns>查询结果</returns>
        public DataTable GetDataTable(string strTmpSelectString)
        {
            DataTable tmpdt = new DataTable();
            SqlDataAdapter tmpsda = new SqlDataAdapter(strTmpSelectString, _Conn);
            try
            {
                OpenConnection();
                tmpsda.Fill(tmpdt);
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return tmpdt;
        }

        /// <summary>
        /// 执行无查询的SQL命令
        /// </summary>
        /// <param name="strTmpCmdString">命令语句</param>
        /// <returns>执行记录条数</returns>
        public int DoCmdExecuteNonQuery(string strTmpCmdString)
        {
            SqlCommand tmpcmd = new SqlCommand(strTmpCmdString, _Conn);
            return DoCmdExecuteNonQuery(tmpcmd);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="tmpcmd">命令语句</param>
        /// <returns>执行记录条数</returns>
        public int DoCmdExecuteNonQuery(SqlCommand tmpcmd)
        {
            tmpcmd.Connection = _Conn;
            int r = 0;
            try
            {
                OpenConnection();
                r = tmpcmd.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return r;
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <param name="strTmpCmdString">查询语句</param>
        /// <returns>查询结果的第一行第一列</returns>
        public Object DoCmdScalarData(string strTmpCmdString)
        {
            SqlCommand tmpcmd = new SqlCommand(strTmpCmdString, _Conn);
            return DoCmdScalarData(tmpcmd);
        }

        /// <summary>
        /// 获取单个数据
        /// </summary>
        /// <param name="tmpcmd">查询语句</param>
        /// <returns>查询结果的第一行第一列</returns>
        public Object DoCmdScalarData(SqlCommand tmpcmd)
        {
            tmpcmd.Connection = _Conn;
            Object r = null;
            try
            {
                OpenConnection();
                r = tmpcmd.ExecuteScalar();
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return r;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="strTmpCmdString">需要进行事务处理的语句</param>
        /// <returns>事务执行结果</returns>
        public bool DoTranscation(string strTmpCmdString)
        {
            SqlCommand tmpcmd = new SqlCommand(strTmpCmdString, _Conn);
            return DoTranscation(tmpcmd);
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="tmpcmd">需要进行事务处理的命令</param>
        /// <returns>事务执行结果</returns>
        public bool DoTranscation(SqlCommand tmpcmd)
        {
            OpenConnection();
            SqlTransaction st = _Conn.BeginTransaction();
            try
            {
                tmpcmd.Transaction = st;
                tmpcmd.ExecuteNonQuery();
                st.Commit();
                return true;
            }
            catch
            {
                st.Rollback();
                return false;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 填充下拉列表框，使用第一列数据
        /// </summary>
        /// <param name="cmb">要填充的ComboBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <param name="ClearItemsBeforeFill">填充以前是否清除原有项目</param>
        public void FillComboBox(ComboBox cmb, string strTmpSelectString, bool ClearItemsBeforeFill)
        {
            FillComboBox(cmb, strTmpSelectString, 0, ClearItemsBeforeFill);
        }

        /// <summary>
        /// 填充列表框
        /// </summary>
        /// <param name="lst">要填充的ListBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <param name="ClearItemsBeforeFill">填充以前是否清除原有项目</param>
        public void FillListBox(ListBox lst, string strTmpSelectString, bool ClearItemsBeforeFill)
        {
            FillListBox(lst, strTmpSelectString, 0, ClearItemsBeforeFill);
        }

        /// <summary>
        /// 填充下拉列表框
        /// </summary>
        /// <param name="cmb">要填充的ComboBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <param name="iColumnIndex">要填充的列索引</param>
        /// <param name="ClearItemsBeforeFill">填充以前是否清除原有项目</param>
        public void FillComboBox(ComboBox cmb, string strTmpSelectString, int iColumnIndex, bool ClearItemsBeforeFill)
        {
            DataTable tmpdt = GetDataTable(strTmpSelectString);
            if (ClearItemsBeforeFill)
            {
                cmb.Items.Clear();
            }
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    cmb.Items.Add(tmpdt.Rows[i][iColumnIndex]);
                }
            }
        }

        /// <summary>
        /// 填充列表框
        /// </summary>
        /// <param name="lst">要填充的ListBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <param name="iColumnIndex">要填充的列索引</param>
        /// <param name="ClearItemsBeforeFill">填充以前是否清除原有项目</param>
        public void FillListBox(ListBox lst, string strTmpSelectString, int iColumnIndex, bool ClearItemsBeforeFill)
        {
            DataTable tmpdt = GetDataTable(strTmpSelectString);
            if (ClearItemsBeforeFill)
            {
                lst.Items.Clear();
            }
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    lst.Items.Add(tmpdt.Rows[i][iColumnIndex]);
                }
            }
        }

        /// <summary>
        /// 查询记录是否存在
        /// </summary>
        /// <param name="strTmpCmdString">查询语句</param>
        /// <returns>是/否</returns>
        public bool RecordExist(string strTmpCmdString)
        {
            DataTable tmpdt = GetDataTable(strTmpCmdString);
            return (tmpdt.Rows.Count > 0);
        }

        /// <summary>
        /// 显示错误信息
        /// </summary>
        /// <param name="strTmpMsg">错误信息</param>
        private void ShowErrMsg(string strTmpMsg)
        {
            MessageBox.Show(strTmpMsg, "错误", MessageBoxButtons.OK);
        }
        #endregion 方法
    }
}
